const { query } = require('../models/db')
const qTable = `select * from loan `,
    orderBy = ` order by create_time desc `,
    limit = ` limit ?,? `,
    whereNameLike = ` where name like ? `,
    whereId = ` where id =? `,
    inStr = ' status in (?,?)',
    qCount = 'select count(*) as rows from loan ';
exports.findLoanByLimitAndName = (start, end, name) => query(qTable + whereNameLike + orderBy + limit, [`%${name}%`, start, end]);
exports.findLoanByLimit = (start, end) => query(qTable + orderBy + limit, [start, end]);
exports.getLoanCountByName = name => query(qCount + whereNameLike, [`%${name}%`])
exports.getLoanCount = () => query(qCount);
exports.insertLoan = obj => {
    let keys = Object.keys(obj);
    return query(`insert into loan (${keys.join(',')}) values (${new Array(keys.length).fill('?').join(',')})`, Object.values(obj))
}
exports.updateLoanByFields = (fileds,id) => {
    let keys = Object.keys(fileds);
    let values = Object.values(fileds);
    return query(`update loan set update_time = now(), ${ keys.join('=?,') + '=? '}`+ whereId,[...values,id])
}
exports.deleteLoanById = id => query('delete from loan'+ whereId,[id]);
exports.findLoanById = id => query(qTable + whereId,[id]);
exports.findLoanByLimitAndFieldsAndName=(start, end,name,status)=>query(qTable + whereNameLike +` and status = ? `+ limit,[`%${name}%`,status,start,end])
exports.findLoanByLimitAndFields = (start,end,fields) => {
    let keys = Object.keys(fields);
    let values = Object.values(fields);
    return query(qTable + ` where  ${keys.join('= ? and ') + '=? '}`+ limit,[...values,start,end])
}
exports.countLoanByFields = (fields) => {
    let keys = Object.keys(fields);
    let values = Object.values(fields);
    return query(`${qCount} where ${keys.join('= ? and ') + '=? '}`,values)
}
exports.findLoanByLimitNameStatusIn = (start,end,name,in1,in2)=> query(`${qTable} ${whereNameLike} and ${inStr} ${limit}`,[`%${name}%`,in1,in2,start,end])
exports.countLoanByNameStatusIn = (name,in1,in2)=> query(`${qCount} ${whereNameLike} and ${inStr}`,[`%${name}%`,in1,in2])
exports.findLoanByLimitStatusIn = (start,end,in1,in2)=> query(`${qTable} where ${inStr} ${limit}`,[in1,in2,start,end])
exports.countLoanByStatusIn = (in1,in2)=> query(`${qCount} where ${inStr}`,[in1,in2])

